package com.newworld.basketballfire.tools;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import java.sql.*;

/**
 * Created by George on 2019/6/2.
 * DataMigration
 */
public class DataMigration {

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        String URL = "jdbc:mysql://127.0.0.1:3306/basketballfire-temp?characterEncoding=utf8&allowMultiQueries=true";
        String USER = "root";
        String PASSWORD = "Test1234_";
        //1.加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        //2.获得数据库链接
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        //3.通过数据库的连接操作数据库，实现增删改查（使用Statement类）
        Statement st = conn.createStatement();
        ResultSet activitySet = st.executeQuery("select * from activity");
        //4.处理数据库的返回结果(使用ResultSet类)
        while (activitySet.next()) {
            String activityId = activitySet.getString("id");
            String users = activitySet.getString("users");
            int usersNum = 0;
            JSONArray usersDatas = JSON.parseArray(users);
            for (int i = 0; i < usersDatas.size(); i++) {
                JSONObject data = usersDatas.getJSONObject(i);
                String userId = data.getString("id");
                int userNum = data.getInteger("number");
                usersNum += userNum;
                String duesId = getDuesIdByActivity(conn, activityId, userId);
                createActivityRecord(conn, activityId, userId, duesId, userNum);
            }
            updateActivity(conn, activityId, usersNum);
        }

        //关闭资源
        activitySet.close();
        st.close();
        conn.close();

    }

    private static boolean updateActivity(Connection conn, String activityId, int usersNum) throws SQLException {
        PreparedStatement state = null;
        try {
            String sql = "UPDATE activity SET user_num=? WHERE id=?";
            state = conn.prepareStatement(sql);
            state.setObject(1, usersNum);
            state.setObject(2, activityId);
            return state.execute();
        } finally {
            if (state != null) {
                state.close();
            }
        }
    }

    private static boolean createActivityRecord(Connection conn, String activityId, String userId, String duesId, int userNum) throws SQLException {
        PreparedStatement state = null;
        try {
            String sql = "INSERT INTO activity_record(activity_id, user_id, dues_id, user_num) VALUES(?,?,?,?)";
            state = conn.prepareStatement(sql);
            state.setObject(1, activityId);
            state.setObject(2, userId);
            state.setObject(3, duesId);
            state.setObject(4, userNum);

            return state.execute();
        } finally {
            if (state != null) {
                state.close();
            }
        }
    }

    private static String getDuesIdByActivity(Connection conn, String activityId, String userId) throws SQLException {
        PreparedStatement state = null;
        ResultSet rs = null;
        try {
            String sql = "SELECT id from dues_record WHERE activity_id=? and user_id=?";
            state = conn.prepareStatement(sql);
            state.setObject(1, activityId);
            state.setObject(2, userId);

            rs = state.executeQuery();
            if (rs.next()) {
                return rs.getString("id");
            }
            return null;
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (state != null) {
                state.close();
            }
        }

    }
}
